/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.futco.sysconthos.Modelo.Admisiones.Daos;

import org.futco.sysconthos.Modelo.Admisiones.Medico;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author randy fernando
 */
public class MedicoDAO implements IDAO<Medico>{
    private Model model;
    private PreparedStatement psInsert;

    public MedicoDAO() throws SQLException {
        model = Model.getInstance();
    }

    public void insert(Medico t) throws SQLException {
        if (psInsert == null) {
            String sqlInsert = "INSERT INTO medico (codigo, id, nombre, apellido, sexo, ciudad, direccion,tel, email) VALUES (?,?,?,?,?,?,?,?,?)";
            psInsert = (PreparedStatement) model.getCon().prepareStatement(sqlInsert);

        psInsert.setString(1, t.getCodigo());
        psInsert.setString(2, t.getId());
        psInsert.setString(3, t.getNombre());
        psInsert.setString(4, t.getApellido());
        psInsert.setString(5, t.getSexo());
        psInsert.setString(6, t.getCiudad());
        psInsert.setString(7, t.getDireccion());
        psInsert.setString(8, t.getTel());
        psInsert.setString(9, t.getEmail());
      //  psInsert.setString(9, t.getCodEspecialidad());
        

        psInsert.executeUpdate();
        }
    }

    public void delete(Medico t) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Medico> getAll() throws SQLException {
      Medico pro=null;
      PreparedStatement ps=null;
      ResultSet rs=null;
      List lista= new LinkedList();

      try{
          String sqlInsert = "Select * from medico";
          ps=(PreparedStatement) model.getCon().prepareStatement(sqlInsert);
          rs=ps.executeQuery();
          while(rs.next()){
              pro=this.loadMedico(rs);
              lista.add(pro);
          }

      }finally{
          if(rs!=null){
              rs.close();
          }
          if(ps!=null){
              ps.close();
          }
      }
      return lista;
    }
    public Medico findCodigo(String code) throws SQLException {
        java.sql.PreparedStatement pst =  null;
        Medico m  = null;
        ResultSet rs = null;
        if(!model.isConnect())
        {
            throw new SQLException(":V");
        }
        pst = model.getCon().prepareStatement("Select * from medico where codigo = ?");
        pst.setString(1,code.trim());
        rs = pst.executeQuery();
        if(rs != null)
        {
          m=this.loadMedico(rs);
        }
        if(rs != null)
        {
            model.getCon().close();
        }
        pst.close();
        rs.close();
        return m;
    }
    public Medico find(Integer id) throws SQLException {
        java.sql.PreparedStatement pst =  null;
        Medico m  = null;
        ResultSet rs = null;
        if(!model.isConnect())
        {
            throw new SQLException(":V");
        }
        pst = model.getCon().prepareStatement("Select * from medico where codigo = ?");
        pst.setString(1, String.valueOf(id));
        rs = pst.executeQuery();
        if(rs != null)
        {
          m=this.loadMedico(rs);
        }
        if(rs != null)
        {
            model.getCon().close();
        }
        pst.close();
        rs.close();
        return m;
    }

    public Medico findIdentificacion(String id) throws SQLException {
       //PreparedStatement pst = null;
        java.sql.PreparedStatement pst =  null;
        Medico p = new Medico();
        ResultSet rs = null;
        pst = model.getCon().prepareStatement("Select * from medico where  id= ?");
        pst.setString(1, id);
        rs = pst.executeQuery();
        if (rs.next()) {
            p=this.loadMedico(rs);
        }
        if (pst != null) {
            pst.close();
        }
        if (rs != null) {
            rs.close();
        }
        return p;
    }
    public void update(String ide, Medico t) throws SQLException {

         String sqlInsert = "UPDATE medico SET codigo = ?, nombre = ?, apellido = ?, sexo = ?, " +
                    "ciudad = ?, direccion = ?, tel = ?, email = ? WHERE id = ?";
            psInsert = (PreparedStatement) model.getCon().prepareStatement(sqlInsert);

            
            psInsert.setString(1, t.getCodigo());
            psInsert.setString(2, t.getNombre());
            psInsert.setString(3, t.getApellido());
            psInsert.setString(4, t.getSexo());         
            psInsert.setString(5, t.getCiudad());
            psInsert.setString(6, t.getDireccion());
            psInsert.setString(7, t.getTel());
            psInsert.setString(8, t.getEmail());
            psInsert.setString(9, ide);
            psInsert.executeUpdate();
    }

    public List<Medico> getAllBy(String parametro, String name) throws SQLException {
         List lista = new LinkedList<Medico>();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        Medico m = null;

        try {
            pst = model.getCon().prepareStatement("SELECT * FROM medico WHERE " + parametro + " like ?");
            pst.setString(1, "%" + name.trim() + "%");
            rs = pst.executeQuery();

            while (rs.next()) {
              m=this.loadMedico(rs);
              lista.add(m);
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return lista;
    }
     public List<Medico> getMedEsp(String codEspe) throws SQLException {
      Medico pro=null;
      PreparedStatement ps=null;
      ResultSet rs=null;
      List lista= new LinkedList();

      try{
          String sqlInsert = "select  * from medico m, especialidad e, especialidadmedico em where e.codigo=em.codEspecialidad and m.codigo=em.codMedico and em.codEspecialidad =" + codEspe;
          ps=(PreparedStatement) model.getCon().prepareStatement(sqlInsert);
          rs=ps.executeQuery();
          while(rs.next()){
              pro=this.loadMedico(rs);
              lista.add(pro);
          }

      }finally{
          if(rs!=null){
              rs.close();
          }
          if(ps!=null){
              ps.close();
          }
      }
      return lista;
    }

    private Medico loadMedico(ResultSet rs) throws SQLException {
       Medico m=new Medico();

       m.setCodigo(rs.getString("codigo"));
       m.setId(rs.getString("id"));
       m.setNombre(rs.getString("nombre"));
       m.setApellido(rs.getString("apellido"));
       m.setSexo(rs.getString("sexo"));
       m.setCiudad(rs.getString("ciudad"));
       m.setDireccion(rs.getString("direccion"));
       m.setTel(rs.getString("tel"));
       m.setEmail(rs.getString("email"));
     
  
      return m;
    }
   
    
}
